﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//using LitJson;
using Common;

namespace SQLDLL
{
    public sealed class SqlHelper : Base
    {
        public SqlHelper() { }


        #region ExecuteDataset

        /// <summary>
        /// 默认查本地库数据
        /// </summary>
        /// <param name="commandtext"></param>
        /// <param name="commandType"></param>
        /// <param name="connstring"></param>
        /// <returns>返回DataSet</returns>
        public static DataSet ExecuteDataset(string commandText, CommandType commandType)
        {
            return ExecuteDataset(commandText, commandType, (SqlParameter[])null);

        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="commandParameters"></param>
        /// <returns>返回DataSet</returns>
        public static DataSet ExecuteDataset(string commandText, CommandType commandType, params SqlParameter[] commandParameters)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection conn = new SqlConnection(Base.ConnStr))
                {

                    conn.Open();
                    SqlCommand cmd = new SqlCommand(commandText, conn);
                    cmd.CommandType = commandType;

                    if (commandParameters != null)
                    {
                        foreach (SqlParameter parm in commandParameters)
                        {
                            cmd.Parameters.Add(parm);
                        }
                    }


                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    da.Fill(ds);
                }
            }
            catch
            {
                //LogHelper.WriteException("出现错误的函数：SqlHelper.ExecuteDataset,commandText:" + commandText , ex);
            }
            if (ds.Tables == null || ds.Tables.Count == 0) { 
                DataTable dt = new DataTable();
                ds.Tables.Add(dt);
            }
            return ds;

        }

        /// <summary>
        /// 传入Conn字符串指定查询哪个接口
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="commandParameters"></param>
        /// <returns>返回DataSet</returns>
        public static DataSet ExecuteDataset(string commandText, CommandType commandType, string ConnStr, params SqlParameter[] commandParameters)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnStr))
                {

                    conn.Open();
                    SqlCommand cmd = new SqlCommand(commandText, conn);
                    cmd.CommandType = commandType;

                    if (commandParameters != null)
                    {
                        foreach (SqlParameter parm in commandParameters)
                        {
                            cmd.Parameters.Add(parm);
                        }
                    }


                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    da.Fill(ds);
                }
            }
            catch
            {
                //LogHelper.WriteException("出现错误的函数：SqlHelper.ExecuteDataset,commandText:" + commandText, ex);
            }
            return ds;

        }

        #endregion


        #region ExecuteReader

        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns>返回DataReader</returns>
        public static SqlDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            return ExecuteReader(commandType, commandText, (SqlParameter[])null);
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns>返回DataReader</returns>
        public static SqlDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            SqlConnection conn = new SqlConnection(Base.ConnStr);

            conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;

            if (commandParameters != null)
            {
                foreach (SqlParameter parm in commandParameters)
                {
                    cmd.Parameters.Add(parm);
                }
            }

            SqlDataReader drd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return drd;
        }

        #endregion


        #region ExecuteNonQuery


        /// <summary>
        /// 执行UPDATE,INSERT,DELETE
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns>所影响的行数</returns>
        public static int ExecuteNonQuery(string ConnectionString,CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(ConnectionString,commandType, commandText, (SqlParameter[])null);
        }


        /// <summary>
        /// 执行UPDATE,INSERT,DELETE
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns>所影响的行数</returns>
        public static int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return ExecuteNonQuery(commandType, commandText, (SqlParameter[])null);
        }



        /// <summary>
        /// 执行UPDATE,INSERT,DELETE
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns>所影响的行数</returns>
        public static int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlConnection conn = new SqlConnection(Base.ConnStr);

            SqlCommand cmd = new SqlCommand();
            conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;

            if (commandParameters != null)
            {
                foreach (SqlParameter parm in commandParameters)
                {
                    cmd.Parameters.Add(parm);
                }
            }

            int retval = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            conn.Close();

            return retval;
        }


        /// <summary>
        /// 执行UPDATE,INSERT,DELETE
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns>所影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString,CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlConnection conn = new SqlConnection(connectionString);

            SqlCommand cmd = new SqlCommand();
            conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;

            if (commandParameters != null)
            {
                foreach (SqlParameter parm in commandParameters)
                {
                    cmd.Parameters.Add(parm);
                }
            }

            int retval = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            conn.Close();

            return retval;
        }



        #endregion

        #region transaction
        /// <summary>
        /// 执行UPDATE,INSERT,DELETE
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns>所影响的行数</returns>
        public static int ExecuteNonQuery(SqlTransaction tran, SqlConnection conn, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = tran;
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;

                if (commandParameters != null)
                {
                    foreach (SqlParameter parm in commandParameters)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }

                int retval = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                return retval;
        }
        #endregion


        #region ExecuteScalar

        /// <summary>
        /// 只需要取一个值,即取第一行第一列的值
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns>返回一个Object类型的值</returns>
        public static object ExecuteScalar(CommandType commandType, string commandText)
        {
            return ExecuteScalar(commandType, commandText, (SqlParameter[])null);
        }



        /// <summary>
        /// 只需要取一个值,即取第一行第一列的值
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns>返回一个Object类型的值</returns>
        public static object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            SqlConnection conn = new SqlConnection(Base.ConnStr);
            SqlCommand cmd = new SqlCommand();
            conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;
            

            if (commandParameters != null)
            {
                foreach (SqlParameter parm in commandParameters)
                {
                    cmd.Parameters.Add(parm);
                }
            }

            object retval = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            conn.Close();

            return retval;

        }

        #endregion


        #region Make Parameter


        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <param name="hisValue"></param>
        /// <returns></returns>
        public static SqlParameter MakeInParameter(string parameterName, SqlDbType dbType, int size, object hisValue)
        {
            return MakeParameter(parameterName, dbType, size, ParameterDirection.Input, hisValue);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public static SqlParameter MakeOutParameter(string parameterName, SqlDbType dbType, int size)
        {
            return MakeParameter(parameterName, dbType, size, ParameterDirection.Output, null);
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <param name="direction"></param>
        /// <param name="hisValue"></param>
        /// <returns></returns>
        public static SqlParameter MakeParameter(string parameterName, SqlDbType dbType, int size, ParameterDirection direction, object hisValue)
        {
            SqlParameter param = (size > 0) ? new SqlParameter(parameterName, dbType, size) : new SqlParameter(parameterName, dbType);

            param.Direction = direction;
            if (!((direction == ParameterDirection.Output) && (hisValue == null)))
            {
                param.Value = hisValue;
            }

            return param;
        }



        #endregion


        #region 分页查询



        #endregion


    }
}
